//
//  TSCMyDBUtil.m
//  Tsc_IOS
//
//  Created by Rio on 15-5-6.
//  Copyright (c) 2015年 idream. All rights reserved.
//

#import "TSCSDK.h"

@implementation TSCMyDBUtil

/**
 * 更新sys_config表中某项的值
 *
 * @date 2015-05-08
 * @author Rio
 */
+ (void) updateSysConfigValueWithKey_1:(NSString *)key value:(NSString *)value {
    [TSCMyDBHelper updateWithTableName:DB_TABLE_NAME_SYS_CONFIG keyValueDictionary:[NSDictionary dictionaryWithObject:value forKey:@"value"] whereDictionary:[NSDictionary dictionaryWithObject:key forKey:@"key"]];
}

/**
 * 批量获得sys_config表中某项的值
 *
 * @date 2015-05-06
 * @author Rio
 * @return 如果找不到，返回空字典
 */
+ (NSDictionary *) getSysConfigValueBatch_1:(NSArray *)keysArr {
    NSMutableDictionary * dic = [[NSMutableDictionary alloc] init];
    NSMutableString *sqlCmd = [NSMutableString stringWithFormat:@"SELECT * FROM %@ WHERE `key` IN (", DB_TABLE_NAME_SYS_CONFIG];
    for (NSInteger i = 0; i < [keysArr count]; i++) {
        if (i == 0) {
            [sqlCmd appendString:[NSString stringWithFormat:@"\"%@\"", keysArr[i]]] ;
        } else {
            [sqlCmd appendString:[NSString stringWithFormat:@",\"%@\"", keysArr[i]]] ;
        }
    }
    [sqlCmd appendString:@");"];
    
    sqlite3 * db = [TSCMyDBHelper openDB];
    sqlite3_stmt * statement;
    if (sqlite3_prepare_v2(db, [sqlCmd UTF8String], -1, &statement, nil) != SQLITE_OK) {
        NSLog(@"[E]查询APP_DB时sql语法不正确！请检查sql:%@", sqlCmd);
        return dic;
    }
    
    while (sqlite3_step(statement) == SQLITE_ROW) {
        char * cstrKey = (char *)sqlite3_column_text(statement, 0);
        char * cstrValue = (char *)sqlite3_column_text(statement, 1);
        NSString * key = [NSString stringWithCString:cstrKey encoding:O_ENCODING];
        NSString * value = [NSString stringWithCString:cstrValue encoding:O_ENCODING];
        
        [dic setObject:value forKey:key];
    }
    
    //    NSLog(@"Dic:%@", dic);
    sqlite3_finalize(statement);
    
    sqlite3_close(db);
    return dic;
}

/**
 * 更新APP_DB中保存的商品分类，及其DB版本号
 *
 * @date 2015-05-06
 * @author Rio
 * @param gcArray 中的每一个元素，都是Dictionary
 */
+ (void) updateGoodsClassWithVersionNum_1:(NSInteger)dbVerGoodsClassSer gcArray:(NSArray*)gcArray {
    if ([gcArray count] > 0) {
        NSLog(@"[D]正在更新APP_DB商品分类表...");
        
        // 删除全表
        [TSCMyDBHelper deleteWithTableName:DB_TABLE_NAME_GOODS_CLASS whereDictionary:nil];
        
        // 一次性插入所有数据
        NSMutableString *sql = [[NSMutableString alloc]init];
        for (NSDictionary * gcDic in gcArray) {
            [sql appendString:[TSCMyDBHelper buildSqlInsertWithTableName:DB_TABLE_NAME_GOODS_CLASS keyValueDictionary:gcDic]];
        }
        
        sqlite3 * db = [TSCMyDBHelper openDB];
        char * error = nil;
        if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &error) != SQLITE_OK) {
            NSLog(@"[E]更新APP_DB商品分类表时，插入新数据失败！%s", error);
            [TSCMyDBHelper closeDB:db];
            return;
        }
        [TSCMyDBHelper closeDB:db];
        
        // 更新版本号
        // 注：更新版本号要放在最后，否则可能会发生更新分类未完成，却因为版本号已经更新了，从而导致再也无法更新APP_DB的商品分类的情况
        [TSCMyDBHelper updateWithTableName:DB_TABLE_NAME_SYS_CONFIG keyValueDictionary:[NSDictionary dictionaryWithObject:[NSNumber numberWithLong:dbVerGoodsClassSer] forKey:@"value"] whereDictionary:[NSDictionary dictionaryWithObject:DB_SYS_CONFIG_KEY_DB_VERSION_GOODS_CLASS forKey:@"key"]];
        
        NSLog(@"[D]更新APP_DB商品分类表完成");
        
    } else {
        NSLog(@"[E]更新APP_DB商品分类表时，传入了空array！");
    }
}

/**
 * 更新APP_DB中保存的城市，及其DB版本号
 *
 * @date 2015-05-08
 * @author Rio
 * @param cityArray 中的每一个元素，都是Dictionary
 */
+ (void) updateCityWithVersionNum_1:(NSInteger)dbVersionCity cityArray:(NSArray *)cityArray {
    if ([cityArray count] > 0) {
        NSLog(@"[D]正在更新城市表...");
        
        // 删除全表
        [TSCMyDBHelper deleteWithTableName:DB_TABLE_NAME_CITY whereDictionary:nil];
        
        // 插入所有新纪录
        NSMutableString *sql = [[NSMutableString alloc]init];
        for (NSDictionary * cityDic in cityArray) {
            [sql appendString:[TSCMyDBHelper buildSqlInsertWithTableName:DB_TABLE_NAME_CITY keyValueDictionary:cityDic]];
        }
        
        sqlite3 * db = [TSCMyDBHelper openDB];
        char * error = nil;
        if (sqlite3_exec(db, [sql UTF8String], NULL, NULL, &error) != SQLITE_OK) {
            NSLog(@"[E]更新APP_DB城市表时，插入新数据失败！%s", error);
            [TSCMyDBHelper closeDB:db];
            return;
        }
        [TSCMyDBHelper closeDB:db];
        
        // 更新版本号
        // 注：更新版本号要放在最后，否则可能会发生更新分类未完成，却因为版本号已经更新了，从而导致再也无法更新APP_DB的商品分类的情况
        [TSCMyDBHelper updateWithTableName:DB_TABLE_NAME_SYS_CONFIG keyValueDictionary:[NSDictionary dictionaryWithObject:[NSNumber numberWithLong:dbVersionCity] forKey:@"value"] whereDictionary:[NSDictionary dictionaryWithObject:DB_SYS_CONFIG_KEY_DB_VERSION_CITY forKey:@"key"]];
        
        NSLog(@"[D]更新APP_DB城市表完成");
    } else {
        NSLog(@"[E]更新APP_DB城市表时，传入了空array！");
    }
}

/**
 * 更新user表。一般用在用户登录后，需要将用户名、邮箱、头像url保存在APP_DB，以便下次登录时，能直接从设备加载用户头像。
 *
 * @author Rio
 * @date 2015-05-21
 */
+ (void) insertOrUpdateUserWithId_1:(NSInteger)Id name:(NSString *)name email:(NSString *)email picUrl:(NSString *)picUrl {
    // 检查输入：id必输，其他选输
    NSAssert(Id > 0, @"[E]%s:Id必须>0！", __FUNCTION__);
    
    // 检查APP_DB中是否已经存在user记录
    NSArray * userArray = [TSCMyDBHelper selectWithTableName:DB_TABLE_NAME_USER columnArray:nil whereDictionary:[NSDictionary dictionaryWithObject:[NSNumber numberWithLong:Id] forKey:@"id"]];
    
    
    // 检查数据是否异常
    NSInteger num = [userArray count];
    NSAssert(num <= 1, @"[E]%s:按照用户名/邮箱选择APP_DB的user表记录时，发现不止一条记录！应该最多只有一条！[Id:]", __FUNCTION__, Id);
    
    // 创建cv
    NSMutableDictionary * keyValueDic = [NSMutableDictionary dictionary];
    [keyValueDic setObject:[NSNumber numberWithLong:Id] forKey:@"id"];
    if ([TSCMyUtil isStringEmpty:name] == false) {
        [keyValueDic setObject:name forKey:@"name"];
    }
    if ([TSCMyUtil isStringEmpty:email] == false) {
        [keyValueDic setObject:email forKey:@"email"];
    }
    if ([TSCMyUtil isStringEmpty:picUrl] == false) {
        [keyValueDic setObject:picUrl forKey:@"pic_url"];
    }
    
    // 存在记录，更新
    if (num == 1) {
        [TSCMyDBHelper updateWithTableName:DB_TABLE_NAME_USER keyValueDictionary:keyValueDic whereDictionary:[NSDictionary dictionaryWithObject:[NSNumber numberWithLong:Id] forKey:@"id"]];
    }
    // 不存在记录，插入
    else {
        // 注：因为通过三方登录时，email可以为空字符串，所以不再对email进行校验。如果email为空字符串，则APP_DB使用自己的默认值
        // 如果为新增记录插入时，name、picUrl都不能为空
        NSAssert([TSCMyUtil isStringEmpty:name] == false && [TSCMyUtil isStringEmpty:picUrl] == false, @"[E]%s:在APP_DB中未找到_id为%li的记录，则需新增一条，所以name、picUrl都不能为空！", __FUNCTION__, Id);
        
        // 当email为空字符串时，记录warning日志
        if ([TSCMyUtil isStringEmpty:email] == true) {
            NSLog(@"[W]%s:向APP_DB新增一条user记录时，email为空！请确认此种情况仅为三方登录时使用，其他情况下如果email为空，则将导致用户无法正常找回密码！", __FUNCTION__);
        }
        
        [TSCMyDBHelper insertWithTableName:DB_TABLE_NAME_USER keyValueDictionary:keyValueDic];
    }
    
}

/**
 * 获得一条user表记录
 *
 * R == Record 表示一条数据表记录，今后凡是从APP_DB中选出的一条完整的记录，都用R表示
 *
 * @date 2015-05-14
 * @author Rio
 */
+ (NSDictionary *) getRUserWithNameOrEmail_1:(NSString *)nameOrEmail {
    NSArray * userArray = [TSCMyDBHelper selectWithSql:[NSString stringWithFormat:@"SELECT * FROM %@ WHERE name = '%@' OR email = '%@';", DB_TABLE_NAME_USER, nameOrEmail, nameOrEmail]];
    if([userArray count] > 0) {
        return userArray[0];
    } else {
        return nil;
    }
}

/**
 * 获得一条user表记录
 *
 * R == Record 表示一条数据表记录，今后凡是从APP_DB中选出的一条完整的记录，都用R表示
 *
 * @date 2015-09-23
 * @author Rio
 */
+ (TSCJsonUser *) getRUserWithUserId_1:(NSInteger)userId {
    NSArray * userArray = [TSCMyDBHelper selectWithSql:[NSString stringWithFormat:@"SELECT * FROM %@ WHERE id = %li", DB_TABLE_NAME_USER, userId]];
    if([userArray count] > 0) {
        NSDictionary * dic = [userArray lastObject];
        
        // 注：APP_DB中仅有id/name/email/pic_url
        TSCJsonUser * jsonUser=  [[TSCJsonUser alloc]init];
        jsonUser.Id = [[dic objectForKey:@"id"] integerValue];
        jsonUser.name = [dic objectForKey:@"name"];
        jsonUser.email = [dic objectForKey:@"email"];
//        jsonUser.role = [[dic objectForKey:@"role"] integerValue];
        jsonUser.picUrl = [dic objectForKey:@"pic_url"];
//        jsonUser.cityId = [[dic objectForKey:@"city_id"] integerValue];
//        jsonUser.tel = [dic objectForKey:@"tel"];
//        jsonUser.shopId = [[dic objectForKey:@"shop_id"] integerValue];
//        jsonUser.emailValid = [[dic objectForKey:@"email_valid"] integerValue];
//        jsonUser.loginKey = [dic objectForKey:@"login_key"];
//        jsonUser.pushUserId = [dic objectForKey:@"push_user_id"];
//        jsonUser.score = [[dic objectForKey:@"score"] integerValue];
//        jsonUser.guideStatus = [[dic objectForKey:@"guide_status"] integerValue];
        
        return jsonUser;
    } else {
        return nil;
    }
}

/**
 * 在给定的商品分类id范围内，选择所有可选择商品分类id范围。比如传入一个3级商品分类的id：id3，则需要选出这个3级商品分类的父类：2
 * 级商品分类id2
 * ，以及这个3级商品分类的爷爷类：1级商品分类id1，并将id3、id2、id1以implode()的方式拼装返回：id3,id2,id1
 * <p>
 * 注：在调用该方法前，请保证传入的ids已经经过一次过滤，不存在：重复项、4级及以下id、非根节点（除3级外），且id必须在APP_DB中存在
 *
 * @author Rio
 * @date 2014-11-10
 *
 * @param availableGCIds
 * @return 如果存在，返回1,2,3；如果不存在，返回空字符串
 */
+ (NSString *) getAllAvailableGCIds_1:(NSString *)availableGCIds {
    // 检查输入参数
    if ([TSCMyUtil isStringEmpty:availableGCIds] == true) {
        return @"";
    }
    
    NSString * tn = DB_TABLE_NAME_GOODS_CLASS;
    NSString * sql = [NSString stringWithFormat:
                      @"SELECT A1.id AS ppid, A2.id AS pid, A3.id AS id%@%@%@%@",
                      [NSString stringWithFormat:@" FROM %@ AS A3", tn],
                      [NSString stringWithFormat:@" LEFT JOIN %@ AS A2 ON (A2.id = A3.pid)", tn],
                      [NSString stringWithFormat:@" LEFT JOIN %@ AS A1 ON (A1.id = A2.pid)", tn],
                      [NSString stringWithFormat:@" WHERE A3.id IN (%@);", availableGCIds]
                      ];
    
    NSInteger Id = 0;
    NSInteger pid = 0;
    NSInteger ppid = 0;
    
    NSArray * idDicArray = [TSCMyDBHelper selectWithSql:sql];
    
    NSMutableArray * resultIdArray = [NSMutableArray array];
    for (NSDictionary * idDic in idDicArray) {
        Id = [[idDic objectForKey:@"id"] intValue]; // id一定不为0
        pid = [idDic objectForKey:@"pid"] == [NSNull null] ? 0 : [[idDic objectForKey:@"pid"] intValue]; // 如果SQL返回NULL，dic中为[NSNull null]
        ppid = [idDic objectForKey:@"ppid"] == [NSNull null] ? 0 : [[idDic objectForKey:@"ppid"] intValue]; // 如果SQL返回NULL，dic中为[NSNull null]
        
        // 添加不为0的id
        [resultIdArray addObject:[NSNumber numberWithLong:Id]];
        if (pid != 0) {
            [resultIdArray addObject:[NSNumber numberWithLong:pid]];
        }
        if (ppid != 0) {
            [resultIdArray addObject:[NSNumber numberWithLong:ppid]];
        }
    }
    
    return [TSCMyUtil implodeWithGlue:@"," array:resultIdArray];
}

/**
 * 对一组商品分类id，假设它们符合以下先决条件：不存在：重复项、4级及以下id、非根节点（除3级外），且id必须在APP_DB中存在，
 * 则生成一个二维数组，该数组保存了从1级分类到这些商品分类id的“路径”。比如：
 * <p>
 * [1,0,0] // 第一行sp1应选择分类1
 * <p>
 * [2,3,0] // 第二行sp1应选择分类2、sp2应选择分类3
 * <p>
 * [4,5,6] // 第三行sp1应选择分类4、sp2应选择分类5、sp3应选择分类6
 *
 * @author Rio
 * @date 2014-11-12
 *
 * @param context
 * @param gcIds
 * @return
 */
+ (void) getSpIdxGCId2DArr_1:(NSInteger [3][3])spIdxGCId2DArr gcIds:(NSString *)gcIds {
    if ([TSCMyUtil isStringEmpty:gcIds] == true) {
        // do nothing
    } else {
        // 解析所有初始时被选择的商品分类
        NSArray * selectedGCIdArr = [TSCMyUtil explodeString:gcIds delimiter:@","];
        
        // 定义二维数组：数组的每一行，记录了选择器一行中3个spinner的被选择商品分类id。比如：
        // [1,0,0] // 第一行sp1应选择分类1
        // [2,3,0] // 第二行sp1应选择分类2、sp2应选择分类3
        // [4,5,6] // 第三行sp1应选择分类4、sp2应选择分类5、sp3应选择分类6
        // 行数 == 初始选择的商品分类id数量
        // 列数 == 3
        //        NSInteger spIdxGCId2DArr[[selectedGCIdArr count]][3];
        
        // 假设被选择的商品分类为3级，尝试选出其父类（pid）、爷爷类（ppid）
        NSString * tn = DB_TABLE_NAME_GOODS_CLASS;
        for (NSInteger i = 0, max = [selectedGCIdArr count]; i < max; i++) {
            NSInteger selectedGCId = [selectedGCIdArr[i] intValue];
            NSString * sql = [NSString stringWithFormat:
                              @"SELECT A1.id AS ppid, A2.id AS pid, A3.id AS id%@%@%@%@",
                              [NSString stringWithFormat:@" FROM %@ AS A3", tn],
                              [NSString stringWithFormat:@" LEFT JOIN %@ AS A2 ON (A2.id = A3.pid)", tn],
                              [NSString stringWithFormat:@" LEFT JOIN %@ AS A1 ON (A1.id = A2.pid)", tn],
                              [NSString stringWithFormat:@" WHERE A3.id = %li;", selectedGCId]
                              ];
            
            NSInteger Id = 0;
            NSInteger pid = 0;
            NSInteger ppid = 0;
            
            NSArray * idDicArray = [TSCMyDBHelper selectWithSql:sql];
            if ([idDicArray count] > 0) {
                NSDictionary * idDic = [idDicArray objectAtIndex:0];
                Id = [[idDic objectForKey:@"id"] intValue]; // id一定不为0
                pid = [idDic objectForKey:@"pid"] == [NSNull null] ? 0 : [[idDic objectForKey:@"pid"] intValue]; // 如果SQL返回NULL，dic中为[NSNull null]
                ppid = [idDic objectForKey:@"ppid"] == [NSNull null] ? 0 : [[idDic objectForKey:@"ppid"] intValue]; // 如果SQL返回NULL，dic中为[NSNull null]
            }
            
            // 如果是1级分类
            if (pid == 0) {
                spIdxGCId2DArr[i][0] = Id;
                spIdxGCId2DArr[i][1] = 0;
                spIdxGCId2DArr[i][2] = 0;
            }
            // 如果是2级分类
            else if (ppid == 0) {
                spIdxGCId2DArr[i][0] = pid;
                spIdxGCId2DArr[i][1] = Id;
                spIdxGCId2DArr[i][2] = 0;
            }
            // 如果是3级分类
            else {
                spIdxGCId2DArr[i][0] = ppid;
                spIdxGCId2DArr[i][1] = pid;
                spIdxGCId2DArr[i][2] = Id;
            }
        }
    }
}

/**
 * 参考getSpIdxGCId2DArr_1()
 *
 * @author Rio
 * @date 2014-11-19
 *
 * @see MyDBUtil#getSpIdxGCId2DArr_1(Context, String)
 * @param context
 * @param selectedGCIds
 * @return
 */
+ (NSArray *) getSpIdxGC2DArr_1:(NSString *)gcIds {
    if ([TSCMyUtil isStringEmpty:gcIds] == true) {
        return [NSArray array]; // 返回空数组
    } else {
        // 解析所有初始时被选择的商品分类
        NSArray * gcIdArr = [TSCMyUtil explodeString:gcIds delimiter:@","];
        
        // 定义二维数组：数组的每一行，记录了选择器一行中3个spinner的被选择商品分类id。比如：
        // [1,0,0] // 第一行sp1应选择分类1
        // [2,3,0] // 第二行sp1应选择分类2、sp2应选择分类3
        // [4,5,6] // 第三行sp1应选择分类4、sp2应选择分类5、sp3应选择分类6
        // 行数 == 初始选择的商品分类id数量
        // 列数 == 3
        NSMutableArray * spIdxGCId2DArr = [NSMutableArray array];
        for (NSInteger i = 0, max = [gcIdArr count]; i < max; i++) { // 行数 == 初始选择的商品分类id数量
            NSMutableArray * arr = [NSMutableArray array]; // 列数 == 3
            [spIdxGCId2DArr addObject:arr];
        }
        
        // 假设被选择的商品分类为3级，尝试选出其父类（pid）、爷爷类（ppid）
        NSString * tn = DB_TABLE_NAME_GOODS_CLASS;
        for (NSInteger i = 0, max = [gcIdArr count]; i < max; i++) {
            NSInteger gcId = [[gcIdArr objectAtIndex:i]intValue];
            NSString * sql = [NSString stringWithFormat:@"%@%@%@%@%@%@%@%@",
                              // cursor_index: 0,1,2
                              @"SELECT A1.id AS ppid, A2.id AS pid, A3.id AS id, ",
                              // cursor_index: 3,4,5,6
                              @" A1.name AS name1, A1.seq AS seq1, A1.status AS status1, A1.pid AS pid1, ",
                              // cursor_index: 7,8,9,10
                              @" A2.name AS name2, A2.seq AS seq2, A2.status AS status2, A2.pid AS pid2, ",
                              // cursor_index: 11,12,13,14
                              @" A3.name AS name3, A3.seq AS seq3, A3.status AS status3, A3.pid AS pid3 ",
                              //
                              [NSString stringWithFormat:@" FROM %@ AS A3", tn],
                              //
                              [NSString stringWithFormat:@" LEFT JOIN %@ AS A2 ON (A2.id = A3.pid)", tn],
                              //
                              [NSString stringWithFormat:@" LEFT JOIN %@ AS A1 ON (A1.id = A2.pid)", tn],
                              //
                              [NSString stringWithFormat:@" WHERE A3.id = %li;", gcId]
                              ];
            
            NSArray * idDicArray = [TSCMyDBHelper selectWithSql:sql];
            
            NSInteger Id = 0;
            NSInteger pid = 0;
            NSInteger ppid = 0;
            NSDictionary * idDic = nil;
            
            if ([idDicArray count] > 0) {
                idDic = [idDicArray objectAtIndex:0];
                Id = [[idDic objectForKey:@"id"] intValue]; // id一定不为0
                pid = [idDic objectForKey:@"pid"] == [NSNull null] ? 0 : [[idDic objectForKey:@"pid"] intValue]; // 如果SQL返回NULL，dic中为[NSNull null]
                ppid = [idDic objectForKey:@"ppid"] == [NSNull null] ? 0: [[idDic objectForKey:@"ppid"] intValue]; // 如果SQL返回NULL，dic中为[NSNull null]
            }
            
            TSCJsonGC * jgcId = nil;
            TSCJsonGC * jgcPid = nil;
            TSCJsonGC * jgcPpid = nil;
            
            // id存在，即A3存在（注：id一定不为0）
            jgcId = [[TSCJsonGC alloc]init];
            jgcId.Id = Id;
            jgcId.name = [idDic objectForKey:@"name3"];
            jgcId.seq = [[idDic objectForKey:@"seq3"] intValue];
            jgcId.status = [[idDic objectForKey:@"status3"] intValue];
            jgcId.pid = [[idDic objectForKey:@"pid3"] intValue];
            
            // pid存在，即A2存在
            if (pid != 0) {
                jgcPid = [[TSCJsonGC alloc]init];
                jgcPid.Id = pid;
                jgcPid.name = [idDic objectForKey:@"name2"];
                jgcPid.seq = [[idDic objectForKey:@"seq2"] intValue];
                jgcPid.status = [[idDic objectForKey:@"status2"] intValue];
                jgcPid.pid = [[idDic objectForKey:@"pid2"] intValue];
            }
            
            // ppid存在，即A1存在
            if (ppid != 0) {
                jgcPpid = [[TSCJsonGC alloc]init];
                jgcPpid.Id = ppid;
                jgcPpid.name = [idDic objectForKey:@"name1"];
                jgcPpid.seq = [[idDic objectForKey:@"seq1"] intValue];
                jgcPpid.status = [[idDic objectForKey:@"status1"] intValue];
                jgcPpid.pid = [[idDic objectForKey:@"pid1"] intValue];
            }
            
            NSMutableArray * arr = [spIdxGCId2DArr objectAtIndex:i];
            
            // 如果是1级分类
            if (pid == 0) {
                [arr addObject:jgcId]; // index == 0
            }
            // 如果是2级分类
            else if (ppid == 0) {
                [arr addObject:jgcPid]; // index == 0
                [arr addObject:jgcId]; // index == 1
            }
            // 如果是3级分类
            else {
                [arr addObject:jgcPpid]; // index == 0
                [arr addObject:jgcPid]; // index == 1
                [arr addObject:jgcId]; // index == 2
            }
        }
        
        return spIdxGCId2DArr;
    }
}

/**
 * 获得某个商品分类的全部直接子类。状态是“正在使用”
 *
 * @author Rio
 * @date 2014-11-06
 *
 * @param context
 * @param pid
 * @param availableGCIds
 *            如果传入空字符串""，则不加IN()限定条件
 * @return
 */
+ (NSArray *) getGoodsClassAllChildrenInUse_1:(NSInteger)pid availableGCIds:(NSString *)availableGCIds {
    
    // 拼装where
    // 注：仅选出正在使用的，而不选择废弃的
    NSMutableString * sql = [NSMutableString stringWithFormat:
                             @"SELECT * FROM %@ WHERE status = %li AND pid = %li",
                             DB_TABLE_NAME_GOODS_CLASS,
                             DB_GOODS_CLASS_STATUS_IN_USE,
                             pid
                             ];
    if ([TSCMyUtil isStringEmpty:availableGCIds] == false) {
        [sql appendString:[NSString stringWithFormat:@" AND id IN(%@)", availableGCIds]];
    }
    [sql appendString:@" ORDER BY seq ASC;"];
    
    NSArray * gcDicArray = [TSCMyDBHelper selectWithSql:sql];
    NSMutableArray * gcJsonArray = [NSMutableArray array];
    for (NSDictionary * gcDic in gcDicArray) {
        TSCJsonGC * gcJson = [[TSCJsonGC alloc] init];
        gcJson.Id = [[gcDic objectForKey:@"id"] intValue];
        gcJson.name = [gcDic objectForKey:@"name"];
        gcJson.seq = [[gcDic objectForKey:@"seq"] intValue];
        gcJson.status = [[gcDic objectForKey:@"status"] intValue];
        gcJson.pid = [[gcDic objectForKey:@"pid"] intValue];
        [gcJsonArray addObject:gcJson];
    }
    return gcJsonArray;
}

/**
 * 获得全部一级商品分类。状态是“正在使用”
 *
 * @author Rio
 * @date 2014-11-06
 *
 * @param context
 * @param userId
 * @param bizNoticeId
 * @return
 */
+ (NSArray *) getGoodsClassTopLevelInUse_1:(NSString *)availableGCIds {
    // 注：选出pid为0的，即为顶级类
    return [TSCMyDBUtil getGoodsClassAllChildrenInUse_1:0 availableGCIds:availableGCIds];
}

/**
 * 获得某个商品分类全量信息，不区分是否in user
 *
 * @author Rio
 * @date 2014-12-07
 *
 * @param context
 * @param gcId
 * @return
 */
+ (TSCJsonGC *) getGC_1:(NSInteger)gcId {
    NSString * sql = [NSString stringWithFormat:
                      @"SELECT * FROM %@ WHERE id = %li",
                      DB_TABLE_NAME_GOODS_CLASS,
                      gcId
                      ];
    NSArray * gcDicArr = [TSCMyDBHelper selectWithSql:sql];
    
    if ([gcDicArr count] > 0) {
        NSDictionary * gcDic = [gcDicArr objectAtIndex:0];
        TSCJsonGC * jsonGC = [[TSCJsonGC alloc]init];
        jsonGC.Id = [[gcDic objectForKey:@"id"] intValue];
        jsonGC.name = [gcDic objectForKey:@"name"];
        jsonGC.seq = [[gcDic objectForKey:@"seq"] intValue];
        jsonGC.status = [[gcDic objectForKey:@"status"] intValue];
        jsonGC.pid = [[gcDic objectForKey:@"pid"] intValue];
        
        return jsonGC;
    } else {
        return nil;
    }
}

/**
 * 获得某用户某种未读消息的数量
 *
 * 注：user_id应该传设备user_id
 *
 * @author Rio
 * @date 2014-11-13
 *
 * @param context
 * @param userId
 * @param types
 *            格式：1,2,3。如果全查，传空字符串
 * @return
 */
+ (NSInteger) getUnreadMsgNumWithUserId_1:(NSInteger)userId types:(NSString *)types isReaded:(NSInteger)isReaded {
    
    NSMutableString * sql = [NSMutableString stringWithFormat:@"SELECT COUNT(*) AS unread_msg_num FROM %@ WHERE user_id = %li", DB_TABLE_NAME_MSG, userId];
    if ([TSCMyUtil isStringEmpty:types] == NO) {
        [sql appendFormat:@" AND type IN (%@)", types];
    }
    if (isReaded != DB_ANY) {
        [sql appendFormat:@" AND is_readed = %li", isReaded];
    }
    NSArray * resultArr = [TSCMyDBHelper selectWithSql:sql];
    if ([resultArr count] > 0) {
        NSDictionary * dic = [resultArr lastObject];
        NSInteger num = [[dic objectForKey:@"unread_msg_num"] integerValue];
        return num;
    } else {
        return 0;
    }
}

/**
 * 查询一条未读消息
 *
 * @author Rio
 * @date 2015-02-11
 *
 * @param context
 * @param userId
 * @param msgId
 * @param type
 * @return
 */
+ (TSCJsonMsg *) getUnreadMsgWithUserId_1:(NSInteger)userId type:(NSInteger)type msgId:(NSInteger)msgId {
    NSMutableString * sql = [NSMutableString stringWithFormat:@"SELECT * FROM %@ WHERE user_id = %li AND type = %li AND msg_id = %li", DB_TABLE_NAME_MSG, userId, type, msgId];
    
    NSArray * resultArr = [TSCMyDBHelper selectWithSql:sql];
    if ([resultArr count] > 0) {
        NSDictionary * dic = [resultArr lastObject];
        
        TSCJsonMsg * jsonMsg = [[TSCJsonMsg alloc]init];
        jsonMsg.userId = [[dic objectForKey:@"user_id"] integerValue];
        jsonMsg.type = [[dic objectForKey:@"type"] integerValue];
        jsonMsg.msgId = [[dic objectForKey:@"msg_id"] integerValue];
        jsonMsg.senderId = [[dic objectForKey:@"sender_id"] integerValue];
        jsonMsg.senderName = [dic objectForKey:@"sender_name"];
        jsonMsg.senderPicUrl = [dic objectForKey:@"sender_pic_url"];
        jsonMsg.info = [dic objectForKey:@"info"];
        jsonMsg.createTime = [[dic objectForKey:@"create_time"] integerValue];
        jsonMsg.isReaded = [[dic objectForKey:@"is_readed"] integerValue];
        jsonMsg.isFavorited = [[dic objectForKey:@"is_favorited"] integerValue];
        
        return jsonMsg;
    } else {
        return nil;
    }
    
}

/**
 * 获得某用户某种未读消息。
 *
 * @author Rio
 * @date 2014-11-13
 *
 * @param context
 * @param userId
 * @param types
 *            格式：1,2,3
 * @param senderName
 * @param createDateStr
 *            格式yyyyMMdd
 * @param lastMsgId
 *            ListView中最后一个JsonMsg.msgId，即上次查询结果集中最后一个记录的msgId
 * @return
 */
+ (NSArray *) getUnreadMsgListWithUserId_1:(NSInteger)userId types:(NSString *)types lastMsgId:(NSInteger)lastMsgId senderName:(NSString *)senderName createDateStr:(NSString *)createDateStr isReaded:(NSInteger)isReaded isFavorite:(NSInteger)isFavorite {
    
    NSMutableString * sql = [NSMutableString stringWithFormat:@"SELECT * FROM %@ WHERE user_id = %li", DB_TABLE_NAME_MSG, userId];
    if ([TSCMyUtil isStringEmpty:types] == NO) {
        [sql appendFormat:@" AND type IN (%@)", types];
    }
    if (lastMsgId > 0) {
        [sql appendFormat:@" AND msg_id < %li", lastMsgId]; // 因为按照创建时间逆序排序，所以要使用小于号
    }
    if ([TSCMyUtil isStringEmpty:senderName] == NO) {
        [sql appendString:@" AND sender_name LIKE \"%"];
        [sql appendString:senderName];
        [sql appendString:@"%\""]; // 注：模糊前缀匹配，此位还没有索引
    }
    if ([TSCMyUtil isStringEmpty:createDateStr] == NO) {
        NSInteger ctStart = [TSCMyUtil getTsFromDateStr:createDateStr format:@"yyyyMMdd"];
        NSInteger ctEnd = [TSCMyUtil getTsTomorrow:ctStart] - 1; // 注：取23:59:59
        
        [sql appendFormat:@" AND create_time >= %li AND create_time <= %li", ctStart, ctEnd];
    }
    if (isReaded != DB_ANY) {
        [sql appendFormat:@" AND is_readed = %li", isReaded];
    }
    if (isReaded != DB_ANY) {
        [sql appendFormat:@" AND is_favorited = %li", isFavorite];
    }
    // 注意排序顺序：一定先按照msg_id逆序排列（因为offset是按照最后一个id取的），不能再按照是否已收藏、已阅读进行排序
    [sql appendString:@" ORDER BY msg_id DESC"];
    [sql appendFormat:@" LIMIT 0, %li", CONFIG_DEFAULT_ROWS];
    
    NSMutableArray * resultArray = [NSMutableArray array];
    NSArray * queryArr = [TSCMyDBHelper selectWithSql:sql];
    for (NSDictionary * dic in queryArr) {
        TSCJsonMsg * jsonMsg=  [[TSCJsonMsg alloc]init];
        jsonMsg.userId = [[dic objectForKey:@"user_id"] integerValue];
        jsonMsg.type = [[dic objectForKey:@"type"] integerValue];
        jsonMsg.msgId = [[dic objectForKey:@"msg_id"] integerValue];
        jsonMsg.senderId = [[dic objectForKey:@"sender_id"] integerValue];
        jsonMsg.senderName = [dic objectForKey:@"sender_name"];
        jsonMsg.senderPicUrl = [dic objectForKey:@"sender_pic_url"];
        jsonMsg.info = [dic objectForKey:@"info"];
        jsonMsg.createTime = [[dic objectForKey:@"create_time"] integerValue];
        jsonMsg.isReaded = [[dic objectForKey:@"is_readed"] integerValue];
        jsonMsg.isFavorited = [[dic objectForKey:@"is_favorited"] integerValue];
        [resultArray addObject:jsonMsg];
    }
    return resultArray;
    
}

/**
 * 插入一条未处理消息
 *
 * @author Rio
 * @date 2014-12-01
 */
+ (BOOL) insertUnreadMsgWithUserId_1:(NSInteger)userId msgType:(NSInteger)msgType msgId:(NSInteger)msgId si:(NSInteger)si sn:(NSString *)sn spu:(NSString *)spu info:(NSString *)info ct:(NSInteger)ct isReaded:(NSInteger)isReaded isFavorited:(NSInteger)isFavorited {
    
    [TSCMyDBHelper insertWithTableName:DB_TABLE_NAME_MSG keyValueDictionary:
     [NSDictionary dictionaryWithObjectsAndKeys:
      [NSNumber numberWithInteger:userId], @"user_id",
      [NSNumber numberWithInteger:msgType], @"type",
      [NSNumber numberWithInteger:msgId], @"msg_id",
      [NSNumber numberWithInteger:si], @"sender_id",
      sn, @"sender_name",
      spu, @"sender_pic_url",
      info, @"info",
      [NSNumber numberWithInteger:ct], @"create_time",
      nil]];
    
    // TODO 如何获取新插入的id？
    
    // 扩散消息
    [[NSNotificationCenter defaultCenter] postNotificationName:NOTIFICATION_NAME_UNREAD_MSG_COME object:nil];
    
    return YES;
    
}

/**
 * 删除全部未处理消息
 *
 * @author Rio
 * @date 2014-12-03
 */
+ (void) deleteAllUnreadMsgWithUserId_1:(NSInteger)userId {
    [TSCMyDBHelper deleteWithTableName:DB_TABLE_NAME_MSG whereDictionary:
     [NSDictionary dictionaryWithObjectsAndKeys:
      [NSNumber numberWithInteger:userId], @"user_id",
      nil]];
    
    // 扩散消息
    [[NSNotificationCenter defaultCenter] postNotificationName:NOTIFICATION_NAME_UNREAD_MSG_DELETED object:nil];
}

/**
 * 删除一条未处理消息
 *
 * @author Rio
 * @date 2014-12-03
 */
+ (void) deleteUnreadMsgWithMsgId_1:(NSInteger)msgId {
    [TSCMyDBHelper deleteWithTableName:DB_TABLE_NAME_MSG whereDictionary:
     [NSDictionary dictionaryWithObjectsAndKeys:
      [NSNumber numberWithInteger:msgId], @"msg_id",
      nil]];
    
    // 扩散消息
    [[NSNotificationCenter defaultCenter] postNotificationName:NOTIFICATION_NAME_UNREAD_MSG_DELETED object:nil];
}


/**
 * 标记一条待处理消息为“已读”状态
 *
 * @author Rio
 * @date 2014-12-03
 */
+ (void) markUnreadMsgReadedWithMsgId_1:(NSInteger)msgId {
    
    [TSCMyDBHelper updateWithTableName:DB_TABLE_NAME_MSG keyValueDictionary:
     [NSDictionary dictionaryWithObjectsAndKeys:
      [NSNumber numberWithInteger:DB_YES], @"is_readed",
      nil] whereDictionary:
     [NSDictionary dictionaryWithObjectsAndKeys:
      [NSNumber numberWithInteger:msgId], @"msg_id",
      nil]];
    
    // 扩散消息
    [[NSNotificationCenter defaultCenter] postNotificationName:NOTIFICATION_NAME_UNREAD_MSG_UPDATED object:nil];
}

/**
 * 标记一条待处理消息（仅针对导购首次回复）为“收藏/未收藏”状态
 *
 * @author Rio
 * @date 2014-12-03
 */
+ (void) markUnreadMsgFavoritedWithMsgId_1:(NSInteger)msgId isFavorited:(NSInteger)isFavorited {
    
    // 参数检查
    NSAssert(isFavorited == DB_YES || isFavorited == DB_NO, @"[E]%s: 标记一条待处理消息（仅针对导购首次回复）为“收藏/未收藏”状态时，传入的收藏状态（%li）不合法！", __FUNCTION__, isFavorited);
    
    [TSCMyDBHelper updateWithTableName:DB_TABLE_NAME_MSG keyValueDictionary:
     [NSDictionary dictionaryWithObjectsAndKeys:
      [NSNumber numberWithInteger:isFavorited], @"is_favorited",
      nil] whereDictionary:
     [NSDictionary dictionaryWithObjectsAndKeys:
      [NSNumber numberWithInteger:msgId], @"msg_id",
      nil]];
    
    // 扩散消息
    [[NSNotificationCenter defaultCenter] postNotificationName:NOTIFICATION_NAME_UNREAD_MSG_UPDATED object:nil];
}


@end
